package com.yayan.xh.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.mysql.jdbc.Statement;

public class TripDao extends ConnectionBase implements ITripDao {

	@Override
	public long insertOne(Map<String, String> map) throws Exception {
		// TODO Auto-generated method stub
		//String sqlStr = "insert into relation(uId,pId,superId) values(?,?,?)";
		String sqlStr = "insert into relation(uId,pId,superId) select ?,?,? from DUAL where not exists(select uId from relation where uId=? and pId=?)";
		Connection conn = getConn();
		conn.setAutoCommit(false);       //不自动提交  
		PreparedStatement ps= conn.prepareStatement(sqlStr,Statement.RETURN_GENERATED_KEYS);
		ps.setLong(1, Long.parseLong(map.get("uId")));
		ps.setLong(2, Long.parseLong(map.get("pId")));
		ps.setLong(3, Long.parseLong(map.get("superId")));
		ps.setLong(4, Long.parseLong(map.get("uId")));
		ps.setLong(5, Long.parseLong(map.get("pId")));
		int re = ps.executeUpdate();
		conn.commit();
		conn.setAutoCommit(true);     //设回自动提交  
		ps.close();
		updateRealName(map.get("name"), Long.parseLong("uId"));
		if(re == 0)
			re = 1;
		return re;
	}

	@Override
	public List<Map<String, Object>> getUserListByCondition(long pId, long superId, int page, int size)
			throws Exception {
		// TODO Auto-generated method stub			
		String sqlStr = "select a.uId,b.realName,b.avatarUrl,b.nickName,superId,c.realName as superName from relation a";
		sqlStr += " join users b on a.uId=b.uId join users c on a.superId=c.uId join products p on p.pId="+pId+" and a.pId=p.pId";
		sqlStr += " where a.uId!=1001";
		String sqlNum = "select COUNT(*) as total from relation where uId!=1001 and pId="+pId;
		if(superId >= 1001){
			sqlStr += " and superId=" + superId;
			sqlNum += " and superId=" + superId;
		}
		sqlStr += " order by superId limit " + (page-1)*size + "," + size;
		QueryRunner qRunner = new QueryRunner();
		List<Map<String, Object>> list = qRunner.query(getConn(),sqlStr, new MapListHandler());
		list.add(0, qRunner.query(getConn(), sqlNum, new MapHandler()));		
		return list;
	}
	
	private void updateRealName(String realName, Long uId) throws Exception{
		String sql = "update users set realName=? where uId=?";
		Connection conn;
		PreparedStatement ps;
		try {
			conn = getConn();
			ps= conn.prepareStatement(sql);
			ps.setString(1, realName);
			ps.setLong(2, uId);
			ps.executeUpdate();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		
	}

	@Override
	public boolean getSuperUserPay(long uId) throws Exception {
		// TODO Auto-generated method stub
		String sql = "select * from relation where uId=? and pId=101";
		QueryRunner qRunner = new QueryRunner();
		List<Map<String, Object>> list = qRunner.query(getConn(), sql, new MapListHandler(), uId);
		if(list == null || list.isEmpty())
			return false;
		return true;
	}

}
